﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Easy.Common;
using Easy.Model;

namespace Easy.DAL
{
    /// <summary>
    /// JsHubDal文件
    /// </summary>
    public class JsHubDAL
    {
        SqlDatabase sqldb=new SqlDatabase(Config.TulingBuyCrmConnectString);

        public DataTable GetJsWithEntityItemName(String type, String EntityName,String ItemName)
        {
            switch (type.Trim().ToLower())
            {
                case "view":
                {
                    return GetViewJsWithEntityItemName(EntityName, ItemName);
                }
                case "form":
                {
                    return GetFormJsWithEntityItemName(EntityName, ItemName);
                }
            }
            return null;
        }

        private DataTable GetFormJsWithEntityItemName(String EntityName, String FormName)
        {
            try
            {
                String Sql =
                @"SELECT JsList.JsContent,JsList.priority FROM FormJsListRelationship LEFT JOIN JsList ON FormJsListRelationship.JsListId=JsList.JsListId WHERE FormJsListRelationship.FormId=(SELECT TOP 1 FormId From Form WHERE Form.EntityName=@entityName AND Form.FormName=@formName) AND JsList.DeletionStateCode=0 ORDER BY JsList.priority DESC";
                var command = sqldb.GetSqlStringCommand(Sql);
                command.Parameters.Add(new SqlParameter("@entityName", EntityName));
                command.Parameters.Add(new SqlParameter("@formName", FormName));
                return sqldb.ExecuteDataSet(command).Tables[0];
            }
            catch (Exception)
            {
                return null;
            }
            
        }

        private DataTable GetViewJsWithEntityItemName(String EntityName, String ViewName)
        {
            try
            {
                String Sql =
                @"SELECT JsList.JsContent,JsList.priority FROM ViewMapJsListRelationship LEFT JOIN JsList ON ViewMapJsListRelationship.JsListId=JsList.JsListId WHERE ViewMapJsListRelationship.ViewMapId=(SELECT TOP 1 ViewMapId From ViewMap WHERE ViewMap.ViewName=@viewName AND ViewMap.EntityName=@entityName) AND JsList.DeletionStateCode=0 ORDER BY JsList.priority DESC";
                var command = sqldb.GetSqlStringCommand(Sql);
                command.Parameters.Add(new SqlParameter("@entityName", EntityName));
                command.Parameters.Add(new SqlParameter("@viewName", ViewName));
                return sqldb.ExecuteDataSet(command).Tables[0];
            }
            catch (Exception)
            {
                return null;
            }
            
        }

        public List<JsList> GetJsListWithEntityItemName(String type, String EntityName, String ItemName)
        {
            DataTable data;
            if (type == null) type = "";
            switch (type.Trim().ToLower())
            {
                case "view":
                    {
                        data= GetJsListWithViewUsingCount(EntityName, ItemName);
                        break;
                    }
                case "form":
                    {
                        data= GetJsListWithFormUsingCount(EntityName, ItemName);
                        break;
                    }
                default:
                {
                    data= GetJsList();
                    break;
                }
            }
            List<JsList> list = new List<JsList>();
            foreach (DataRow dataRow in data.Rows)
            {
                list.Add(new JsList()
                {
                    JsListId = Guid.Parse(dataRow["JsListId"].ToString()),
                    JsCount = int.Parse(dataRow["Count"].ToString()),
                    JsName = dataRow["JsName"].ToString()
                });
            }
            return list;
        }

        private DataTable GetJsList()
        {
            String Sql = "SELECT JsList.JsListId AS JsListId ,Name AS JsName,0 AS Count FROM JsList WHERE JsList.DeletionStateCode=0";
            var command = sqldb.GetSqlStringCommand(Sql);
            return sqldb.ExecuteDataSet(command).Tables[0];
        }

        private DataTable GetJsListWithViewUsingCount(String EntityName, String ViewName)
        {
            String Sql =
                "SELECT JsList.JsListId AS JsListId,Name AS JsName,COUNT(ViewMapJsListRelationShip.JsListId) AS Count FROM JsList LEFT JOIN ViewMapJsListRelationship ON ViewMapJsListRelationship.JsListId=JsList.JsListId AND ViewMapId=(SELECT TOP 1 ViewMapId From ViewMap WHERE ViewMap.ViewName=@viewName AND ViewMap.EntityName=@entityName) WHERE JsList.DeletionStateCode=0 GROUP BY JsList.JsListId,ViewMapJsListRelationship.JsListId,Name";
            var command = sqldb.GetSqlStringCommand(Sql);
            command.Parameters.Add(new SqlParameter("@entityName", EntityName));
            command.Parameters.Add(new SqlParameter("@viewName", ViewName));
            return sqldb.ExecuteDataSet(command).Tables[0];
        }

        private DataTable GetJsListWithFormUsingCount(String EntityName, String FormName)
        {
            String Sql =
                "SELECT JsList.JsListId AS JsListId,Name AS JsName,COUNT(FormJsListRelationship.JsListId) AS Count FROM JsList LEFT JOIN FormJsListRelationship ON FormJsListRelationship.JsListId=JsList.JsListId AND FormId=(SELECT TOP 1 FormId From Form WHERE Form.EntityName=@entityName AND Form.FormName=@formName) WHERE JsList.DeletionStateCode=0 GROUP BY JsList.JsListId,FormJsListRelationship.JsListId,Name";
            var command = sqldb.GetSqlStringCommand(Sql);
            command.Parameters.Add(new SqlParameter("@entityName", EntityName));
            command.Parameters.Add(new SqlParameter("@formName", FormName));
            return sqldb.ExecuteDataSet(command).Tables[0];
        }

        public Guid DeleteViewAllJsRelation(String EntityName, String ViewName)
        {
            String Sql =
                "DECLARE @Uid uniqueidentifier SELECT TOP 1 @Uid=ViewMapId From ViewMap WHERE ViewMap.ViewName=@viewName AND ViewMap.EntityName=@entityName SELECT @Uid as UID DELETE FROM ViewMapJsListRelationship WHERE ViewMapId=@Uid";
            var command = sqldb.GetSqlStringCommand(Sql);
            command.Parameters.Add(new SqlParameter("@entityName", EntityName));
            command.Parameters.Add(new SqlParameter("@viewName", ViewName));
            
            try
            {
                var data = sqldb.ExecuteDataSet(command);
                return Guid.Parse(data.Tables[0].Rows[0]["UID"].ToString());
            }
            catch (Exception)
            {
                return Guid.Empty;
            }
        }

        private bool SaveViewJsRelationshipSingle(Guid ViewId,Guid JsListId)
        {
            try
            {
                String Sql =
                "INSERT INTO ViewMapJsListRelationship(ViewMapJsListRelationshipId,ViewMapId,JsListId) VALUES (newid(),@viewId,@jslistId)";
                var command = sqldb.GetSqlStringCommand(Sql);
                command.Parameters.Add(new SqlParameter("@viewId", ViewId));
                command.Parameters.Add(new SqlParameter("@jslistId", JsListId));
                sqldb.ExecuteNonQuery(command);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool SaveViewJsRelationshipMulti(List<Guid> JsListIdList, Guid ViewId)
        {
            foreach (Guid id in JsListIdList)
            {
                SaveViewJsRelationshipSingle(ViewId, id);
            }
            return true;
        }



        public Guid DeleteFormAllJsRelation(String EntityName, String FormName)
        {
            String Sql =
                "DECLARE @Uid uniqueidentifier SELECT TOP 1 @Uid=FormId From Form WHERE Form.EntityName=@entityName AND Form.FormName=@formName SELECT @Uid as UID DELETE FROM FormJsListRelationship WHERE FormId=@Uid";
            var command = sqldb.GetSqlStringCommand(Sql);
            command.Parameters.Add(new SqlParameter("@entityName", EntityName));
            command.Parameters.Add(new SqlParameter("@formName", FormName));

            try
            {
                var data = sqldb.ExecuteDataSet(command);
                return Guid.Parse(data.Tables[0].Rows[0]["UID"].ToString());
            }
            catch (Exception)
            {
                return Guid.Empty;
            }
        }

        private bool SaveFormJsRelationshipSingle(Guid FormId, Guid JsListId)
        {
            try
            {
                String Sql =
                "INSERT INTO FormJsListRelationship(FormJsListRelationship,FormId,JsListId) VALUES (newid(),@formId,@jslistId)";
                var command = sqldb.GetSqlStringCommand(Sql);
                command.Parameters.Add(new SqlParameter("@formId", FormId));
                command.Parameters.Add(new SqlParameter("@jslistId", JsListId));
                sqldb.ExecuteNonQuery(command);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool SaveFormJsRelationshipMulti(List<Guid> JsListIdList, Guid FormId)
        {
            foreach (Guid id in JsListIdList)
            {
                SaveFormJsRelationshipSingle(FormId, id);
            }
            return true;
        }

    }
}